using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using SouthernLand.Common.BOL;

namespace SouthernLand.Common.DAL
{
    public class NewsLetterDAL : BaseDAL
    {
        #region ObjIDataReader

        /// <summary>
        /// Returns a new NewsLetter instance filled with the DataReader's current record data 
        /// </summary>
        private NewsLetter GetNewsLetterFromReader(IDataReader dr)
        {
            var obj = new NewsLetter();
            obj.NewsLetterID = (dr["NewsLetterID"] is DBNull) ? Int32.MinValue : (Int32) dr["NewsLetterID"];
            obj.UserID = (dr["UserID"] is DBNull) ? String.Empty : (String) dr["UserID"];
            obj.AddedDate = (dr["AddedDate"] is DBNull) ? DateTime.MinValue : (DateTime) dr["AddedDate"];
            obj.Title = (dr["Title"] is DBNull) ? String.Empty : (String) dr["Title"];
            obj.FirstName = (dr["FirstName"] is DBNull) ? String.Empty : (String) dr["FirstName"];
            obj.LastName = (dr["LastName"] is DBNull) ? String.Empty : (String) dr["LastName"];
            obj.Email = (dr["Email"] is DBNull) ? String.Empty : (String) dr["Email"];
            obj.Company = (dr["Company"] is DBNull) ? String.Empty : (String) dr["Company"];
            return obj;
        }

        #endregion

        /// <summary>
        /// Returns a collection of NewsLetter with the data read from the input DataReader
        /// </summary>
        private List<NewsLetter> GetNewsLetterCollectionFromReader(IDataReader dr)
        {
            var obj = new List<NewsLetter>();
            while (dr.Read())
                obj.Add(GetNewsLetterFromReader(dr));
            return obj;
        }

        public Boolean DeleteByEmail(String Email)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("Job_NewsLetter_DeleteByEmail", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@Email", SqlDbType.VarChar).Value = Email;
                int ret = myCommand.ExecuteNonQuery();
                return (ret == 1);
            }
        }

        public Boolean CheckEmail(String Email)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                try
                {
                    var myCommand = new SqlCommand("Job_NewsLetter_CheckEmail", myConn);
                    myCommand.CommandType = CommandType.StoredProcedure;
                    myCommand.Parameters.Add("@Email", SqlDbType.VarChar).Value = Email;
                    var value = (String) myCommand.ExecuteScalar();
                    if (value.Trim().Length > 2)
                        return true;
                    return false;
                }
                catch
                {
                    return false;
                }
            }
        }

        public int Insert(NewsLetter obj)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("Job_NewsLetter_Insert", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@NewsLetterID", SqlDbType.Int).Direction = ParameterDirection.Output;
                myCommand.Parameters.Add("@UserID", SqlDbType.NVarChar).Value = obj.UserID;
                myCommand.Parameters.Add("@AddedDate", SqlDbType.DateTime).Value = obj.AddedDate;
                myCommand.Parameters.Add("@Title", SqlDbType.NVarChar).Value = obj.Title;
                myCommand.Parameters.Add("@FirstName", SqlDbType.NVarChar).Value = obj.FirstName;
                myCommand.Parameters.Add("@LastName", SqlDbType.NVarChar).Value = obj.LastName;
                myCommand.Parameters.Add("@Email", SqlDbType.VarChar).Value = obj.Email;
                myCommand.Parameters.Add("@Company", SqlDbType.NVarChar).Value = obj.Company;

                int ret = myCommand.ExecuteNonQuery();
                return (int) myCommand.Parameters["@NewsLetterID"].Value;
            }
        }

        public bool Update(NewsLetter obj)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("Job_NewsLetter_Update", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@NewsLetterID", SqlDbType.Int).Value = obj.NewsLetterID;
                myCommand.Parameters.Add("@UserID", SqlDbType.NVarChar).Value = obj.UserID;
                myCommand.Parameters.Add("@AddedDate", SqlDbType.DateTime).Value = obj.AddedDate;
                myCommand.Parameters.Add("@Title", SqlDbType.NVarChar).Value = obj.Title;
                myCommand.Parameters.Add("@FirstName", SqlDbType.NVarChar).Value = obj.FirstName;
                myCommand.Parameters.Add("@LastName", SqlDbType.NVarChar).Value = obj.LastName;
                myCommand.Parameters.Add("@Email", SqlDbType.VarChar).Value = obj.Email;
                myCommand.Parameters.Add("@Company", SqlDbType.NVarChar).Value = obj.Company;

                int ret = myCommand.ExecuteNonQuery();
                return (ret == 1);
            }
        }

        public bool Delete(Int32 NewsLetterID)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("Job_NewsLetter_Delete", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@NewsLetterID", SqlDbType.Int).Value = NewsLetterID;

                int ret = myCommand.ExecuteNonQuery();
                return (ret == 1);
            }
        }

        /// <summary>
        /// Retrieves the Job_NewsLetter with the specified ID
        /// </summary>
        public NewsLetter GetNewsLetterByID(Int32 NewsLetterID)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("Job_NewsLetter_GetNewsLetterByID", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@NewsLetterID", SqlDbType.Int).Value = NewsLetterID;

                SqlDataReader dr = myCommand.ExecuteReader();
                return (dr.Read() ? GetNewsLetterFromReader(dr) : null);
            }
        }

        //Get All NewsLetter
        public List<NewsLetter> GetAllNewsLetter(int PageIndex, int PageSize)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("Job_NewsLetter_GetAllNewsLetter", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = PageIndex;
                myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = PageSize;
                SqlDataReader dr = myCommand.ExecuteReader();
                return GetNewsLetterCollectionFromReader(dr);
            }
        }

        // Get Count All records of Table  NewsLetter
        public int GetCountNewsLetter()
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("Job_NewsLetter_GetCountNewsLetter", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                return (int) myCommand.ExecuteScalar();
            }
        }
    }
}